using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Runtime.Serialization;
using System.Collections;
using System.Data;
using System.ComponentModel;
using System.Data.OracleClient;
using WPFExcelReport.Helpers;
using WPFExcelReport.Controls;
namespace WPFExcelReport
{
    public partial class VMAVBSTG
    {
        public DataRow Convert2MAVBSTG(DataRow dwInput, string ITXDBVERSION, bool IsConvert = false)
        {
            DataSet SResult = new DataSet();
            SResult = QueryMAVBSTG(dwInput, 0);
            DataRow dwReturn = null;
            if (SResult.Tables[0].Rows.Count > 0)
            {
                dwReturn = SResult.Tables[0].Rows[0];
            }
            else
            {
                dwReturn = SResult.Tables[0].NewRow();
            }
            switch (ITXDBVERSION)
            {
                case "YGR":
                    #region=========
         dwReturn["MVGMANDANT"] = " ".PadRight(2 ); 
         dwReturn["MVGAUFKRS"] = " ".PadRight(2 ); 
         dwReturn["MVGAUFLFD"] = " ".PadRight(6 ); 
         dwReturn["MVGAUFPOS"] = " ".PadRight(3 ); 
         dwReturn["MVGPSA"] = " ".PadRight(1 ); 
         dwReturn["MVGUPOS"] = " ".PadRight(4 ); 
         dwReturn["MVGAUFART"] = " ".PadRight(2 ); 
         dwReturn["MVGERLDAT"] = " ".PadRight(0 ); 
         dwReturn["MVGDIAUFKRS"] = " ".PadRight(2 ); 
         dwReturn["MVGDIAUFLFD"] = " ".PadRight(6 ); 
         dwReturn["MVGDIAUFPOS"] = " ".PadRight(3 ); 
         dwReturn["MVGISTKOSTERM"] = " ".PadRight(1 ); 
         dwReturn["MVGPRGR"] = " ".PadRight(3 ); 
         dwReturn["MVGARTGRP"] = " ".PadRight(3 ); 
         dwReturn["MVGWAKZ"] = " ".PadRight(1 ); 
         dwReturn["MVGKUNDNR"] = " ".PadRight(8 ); 
         dwReturn["MVGWERK"] = " ".PadRight(3 ); 
         dwReturn["MVGABT"] = " ".PadRight(3 ); 
         dwReturn["MVGART"] = " ".PadRight(20 ); 
         dwReturn["MVGVAR"] = " ".PadRight(3 ); 
         dwReturn["MVGFARBE"] = " ".PadRight(10 ); 
         dwReturn["MVGDESS"] = " ".PadRight(10 ); 
         dwReturn["MVGCOLORIT"] = " ".PadRight(10 ); 
         dwReturn["MVGBESCHICH"] = " ".PadRight(10 ); 
         dwReturn["MVGZUSTAND"] = " ".PadRight(4 ); 
         dwReturn["MVGKANR"] = " ".PadRight(8 ); 
         dwReturn["MVGKAPOS"] = " ".PadRight(4 ); 
         dwReturn["MVGVB"] = " ".PadRight(3 ); 
         dwReturn["MVGVG"] = " ".PadRight(3 ); 
         dwReturn["MVGLOSGR"] = " ".PadRight(0 ); 
         dwReturn["MVGAUFGR"] = " ".PadRight(0 ); 
         dwReturn["MVGGART"] = " ".PadRight(20 ); 
         dwReturn["MVGGVAR"] = " ".PadRight(3 ); 
         dwReturn["MVGGFARBE"] = " ".PadRight(10 ); 
         dwReturn["MVGPLANVBR"] = " ".PadRight(0 ); 
         dwReturn["MVGPLANABF"] = " ".PadRight(0 ); 
         dwReturn["MVGPLANABFPRZ"] = " ".PadRight(0 ); 
         dwReturn["MVGPLANKOST"] = " ".PadRight(0 ); 
         dwReturn["MVGSOLLVBR"] = " ".PadRight(0 ); 
         dwReturn["MVGSOLLABF"] = " ".PadRight(0 ); 
         dwReturn["MVGSOLLABFPRZ"] = " ".PadRight(0 ); 
         dwReturn["MVGSOLLKOST"] = " ".PadRight(0 ); 
         dwReturn["MVGISTVBR"] = " ".PadRight(0 ); 
         dwReturn["MVGISTABF"] = " ".PadRight(0 ); 
         dwReturn["MVGISTABFPRZ"] = " ".PadRight(0 ); 
         dwReturn["MVGISTKOST"] = " ".PadRight(0 ); 
         dwReturn["MVGISTKOSTDPR"] = " ".PadRight(0 ); 
         dwReturn["MVGISTKOSTPPR"] = " ".PadRight(0 ); 
         dwReturn["MVGFILLER"] = " ".PadRight(67 ); 
                    #endregion
                    break;
                case "JLT":

                    break;
                default:

                    break;
            }
            if (IsConvert)
            {
                foreach (DataColumn cm in dwInput.Table.Columns)
                {
                    if (dwInput[cm.ColumnName].ToString() == "")
                        continue;
                    if (dwReturn.Table.Columns.Contains(cm.ColumnName))
                    {
                        dwReturn[cm.ColumnName] = dwInput[cm.ColumnName];
                    }
                }
            }
            return dwReturn;
        }
        public void MapMAVBSTG(ref DataRow dwLocal, DataRow dwData)
        {
            BlindMap(ref dwLocal, dwData);
        }
        public void SaveMAVBSTG(DataRow dwData, string ITXDBVERSION = "50.24")
        {
            DataSet setResult = new DataSet();
            DataTable tbLocal = null;
            DataRow dwLocal = null;
            bool flgExist = false;
            setResult = QueryMAVBSTG(dwData);
            if (setResult.Tables.Count > 0)
            {
                tbLocal = setResult.Tables[0];
                if (tbLocal.Rows.Count > 0)
                {
                    flgExist = true;
                    dwLocal = tbLocal.Rows[0];
                }
                else
                {
                    dwLocal = tbLocal.NewRow();
                }
            }
            MapMAVBSTG(ref dwLocal, dwData);

            if (!flgExist)
            {

                InsertMAVBSTG(dwLocal, ITXDBVERSION);
            }
            UpdateMAVBSTG(dwLocal);
        }
        public void InsertMAVBSTG(DataRow dwData, string ITXDBVERSION = "50.24")
        {
            OracleCommand or1;
            or1 = OracleDB.CreateCommand();
            switch (ITXDBVERSION)
            {
                case "YGR":
                    or1.CommandText = "INSERT INTO MAVBSTG(VAKBMANDANT, VAKBKOLL, VAKBART, VAKBKUNDNR, VAKBFARBE, VAKBKDARTNR, VAKBKDARTBEZ, VAKBKDFARBNR, VAKBANDRUCK, VAKBMEGEW, VAKBMENGEH, VAKBGEWVON, VAKBGEWBIS, VAKBGEWCA, VAKBGEWFKT, VAKBGEWGRP, VAKBMEGFKT, VAKBMEGGRP, VAKBAENDZL, VAKBAENDBEN, VAKBAENDDAT, VAKBNEUBEN, VAKBNEUDAT, VAKBFILLER)"
                        + " VALUES (:VAKBMANDANT, :VAKBKOLL, :VAKBART, :VAKBKUNDNR, :VAKBFARBE, '                         ', '                         ', '                             ', ' ', '   ', '   ', 0, 0, ' ', '000000000000', ' ', '000000000000', ' ', 0, '        ', '000000', '        ', '      ', '                                          ')";
                    or1.Parameters.Add("VAKBMANDANT", OracleType.NVarChar).Value = dwData["VAKBMANDANT"].ToString();
                    or1.Parameters.Add("VAKBKOLL", OracleType.NVarChar).Value = dwData["VAKBKOLL"].ToString();
                    or1.Parameters.Add("VAKBART", OracleType.NVarChar).Value = dwData["VAKBART"].ToString();
                    or1.Parameters.Add("VAKBFARBE", OracleType.NVarChar).Value = dwData["VAKBFARBE"].ToString();
                    or1.Parameters.Add("VAKBKUNDNR", OracleType.NVarChar).Value = dwData["VAKBKUNDNR"].ToString();
                    break;
                //VAFMANDANT VAFKOLL VAFART VAFFARBE VAFBEZ VAFFFARBE

            }
            
            Connect2Proxy(or1);
        }
        public DataSet QueryMAVBSTG(DataRow dwData, int option = 1)
        {
            DataSet Return;
            DataSet Result = new DataSet();
            OracleCommand or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 0:
                    or1.CommandText = "SELECT * FROM MAVBSTG WHERE  ROWNUM<2 ";
                    break;
              
                case 99:
                    or1.CommandText = "SELECT * FROM MAVBSTG  WHERE ROWNUM<50";
                    break;
                default:
                    break;
            }
            Result = Connect2Proxy(or1);
            if (Result.Tables.Count > 0)
                Result.Tables[0].TableName = "MAVBSTG";
            Return = Result;
            return Return;
        }
        public void DeleteMAVBSTG(DataRow dwData, int option = 1)
        {

            OracleCommand or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    or1.CommandText = "DELETE FROM MAVBSTG   WHERE  VAKBMANDANT=:VAKBMANDANT AND VAKBKOLL=:VAKBKOLL AND VAKBART =:VAKBART AND VAKBFARBE=:VAKBFARBE AND VAKBKUNDNR=:VAKBKUNDNR";
                     or1.Parameters.Add("VAKBMANDANT", OracleType.NVarChar).Value = dwData["VAKBMANDANT"].ToString();
                    or1.Parameters.Add("VAKBKOLL", OracleType.NVarChar).Value = dwData["VAKBKOLL"].ToString();
                    or1.Parameters.Add("VAKBART", OracleType.NVarChar).Value = dwData["VAKBART"].ToString();
                    or1.Parameters.Add("VAKBFARBE", OracleType.NVarChar).Value = dwData["VAKBFARBE"].ToString();
                    or1.Parameters.Add("VAKBKUNDNR", OracleType.NVarChar).Value = dwData["VAKBKUNDNR"].ToString();
                    break;
                default:
                    break;
            }
            Connect2Proxy(or1);
        } 
      
        public void UpdateMAVBSTG(DataRow dwData, string ITXDBVERSION = "50.24", int option = 1)
        {
            OracleCommand or1;
            DataSet Result = new DataSet();
            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    #region====Command==== 
                    or1.CommandText = "UPDATE MAVBSTG SET "
                    +"MVGMANDANT= :MVGMANDANT,"
                    +"MVGAUFKRS= :MVGAUFKRS,"
                    +"MVGAUFLFD= :MVGAUFLFD,"
                    +"MVGAUFPOS= :MVGAUFPOS,"
                    +"MVGPSA= :MVGPSA,"
                    +"MVGUPOS= :MVGUPOS,"
                    +"MVGAUFART= :MVGAUFART,"
                    +"MVGERLDAT= :MVGERLDAT,"
                    +"MVGDIAUFKRS= :MVGDIAUFKRS,"
                    +"MVGDIAUFLFD= :MVGDIAUFLFD,"
                    +"MVGDIAUFPOS= :MVGDIAUFPOS,"
                    +"MVGISTKOSTERM= :MVGISTKOSTERM,"
                    +"MVGPRGR= :MVGPRGR,"
                    +"MVGARTGRP= :MVGARTGRP,"
                    +"MVGWAKZ= :MVGWAKZ,"
                    +"MVGKUNDNR= :MVGKUNDNR,"
                    +"MVGWERK= :MVGWERK,"
                    +"MVGABT= :MVGABT,"
                    +"MVGART= :MVGART,"
                    +"MVGVAR= :MVGVAR,"
                    +"MVGFARBE= :MVGFARBE,"
                    +"MVGDESS= :MVGDESS,"
                    +"MVGCOLORIT= :MVGCOLORIT,"
                    +"MVGBESCHICH= :MVGBESCHICH,"
                    +"MVGZUSTAND= :MVGZUSTAND,"
                    +"MVGKANR= :MVGKANR,"
                    +"MVGKAPOS= :MVGKAPOS,"
                    +"MVGVB= :MVGVB,"
                    +"MVGVG= :MVGVG,"
                    +"MVGLOSGR= :MVGLOSGR,"
                    +"MVGAUFGR= :MVGAUFGR,"
                    +"MVGGART= :MVGGART,"
                    +"MVGGVAR= :MVGGVAR,"
                    +"MVGGFARBE= :MVGGFARBE,"
                    +"MVGPLANVBR= :MVGPLANVBR,"
                    +"MVGPLANABF= :MVGPLANABF,"
                    +"MVGPLANABFPRZ= :MVGPLANABFPRZ,"
                    +"MVGPLANKOST= :MVGPLANKOST,"
                    +"MVGSOLLVBR= :MVGSOLLVBR,"
                    +"MVGSOLLABF= :MVGSOLLABF,"
                    +"MVGSOLLABFPRZ= :MVGSOLLABFPRZ,"
                    +"MVGSOLLKOST= :MVGSOLLKOST,"
                    +"MVGISTVBR= :MVGISTVBR,"
                    +"MVGISTABF= :MVGISTABF,"
                    +"MVGISTABFPRZ= :MVGISTABFPRZ,"
                    +"MVGISTKOST= :MVGISTKOST,"
                    +"MVGISTKOSTDPR= :MVGISTKOSTDPR,"
                    +"MVGISTKOSTPPR= :MVGISTKOSTPPR,"
                    +"MVGFILLER= :MVGFILLER,"
                        + " WHERE  "
                        + "  VAKBMANDANT=:VAKBMANDANT AND VAKBKOLL=:VAKBKOLL AND VAKBART =:VAKBART AND VAKBFARBE=:VAKBFARBE AND VAKBKUNDNR=:VAKBKUNDNR";
                    #endregion

                    #region====Parameters====
          or1.Parameters.Add("MVGMANDANT", OracleType.NVarChar).Value = dwData["MVGMANDANT"].ToString(); 
          or1.Parameters.Add("MVGAUFKRS", OracleType.NVarChar).Value = dwData["MVGAUFKRS"].ToString(); 
          or1.Parameters.Add("MVGAUFLFD", OracleType.NVarChar).Value = dwData["MVGAUFLFD"].ToString(); 
          or1.Parameters.Add("MVGAUFPOS", OracleType.NVarChar).Value = dwData["MVGAUFPOS"].ToString(); 
          or1.Parameters.Add("MVGPSA", OracleType.NVarChar).Value = dwData["MVGPSA"].ToString(); 
          or1.Parameters.Add("MVGUPOS", OracleType.NVarChar).Value = dwData["MVGUPOS"].ToString(); 
          or1.Parameters.Add("MVGAUFART", OracleType.NVarChar).Value = dwData["MVGAUFART"].ToString(); 
          or1.Parameters.Add("MVGERLDAT", OracleType.NVarChar).Value = dwData["MVGERLDAT"].ToString(); 
          or1.Parameters.Add("MVGDIAUFKRS", OracleType.NVarChar).Value = dwData["MVGDIAUFKRS"].ToString(); 
          or1.Parameters.Add("MVGDIAUFLFD", OracleType.NVarChar).Value = dwData["MVGDIAUFLFD"].ToString(); 
          or1.Parameters.Add("MVGDIAUFPOS", OracleType.NVarChar).Value = dwData["MVGDIAUFPOS"].ToString(); 
          or1.Parameters.Add("MVGISTKOSTERM", OracleType.NVarChar).Value = dwData["MVGISTKOSTERM"].ToString(); 
          or1.Parameters.Add("MVGPRGR", OracleType.NVarChar).Value = dwData["MVGPRGR"].ToString(); 
          or1.Parameters.Add("MVGARTGRP", OracleType.NVarChar).Value = dwData["MVGARTGRP"].ToString(); 
          or1.Parameters.Add("MVGWAKZ", OracleType.NVarChar).Value = dwData["MVGWAKZ"].ToString(); 
          or1.Parameters.Add("MVGKUNDNR", OracleType.NVarChar).Value = dwData["MVGKUNDNR"].ToString(); 
          or1.Parameters.Add("MVGWERK", OracleType.NVarChar).Value = dwData["MVGWERK"].ToString(); 
          or1.Parameters.Add("MVGABT", OracleType.NVarChar).Value = dwData["MVGABT"].ToString(); 
          or1.Parameters.Add("MVGART", OracleType.NVarChar).Value = dwData["MVGART"].ToString(); 
          or1.Parameters.Add("MVGVAR", OracleType.NVarChar).Value = dwData["MVGVAR"].ToString(); 
          or1.Parameters.Add("MVGFARBE", OracleType.NVarChar).Value = dwData["MVGFARBE"].ToString(); 
          or1.Parameters.Add("MVGDESS", OracleType.NVarChar).Value = dwData["MVGDESS"].ToString(); 
          or1.Parameters.Add("MVGCOLORIT", OracleType.NVarChar).Value = dwData["MVGCOLORIT"].ToString(); 
          or1.Parameters.Add("MVGBESCHICH", OracleType.NVarChar).Value = dwData["MVGBESCHICH"].ToString(); 
          or1.Parameters.Add("MVGZUSTAND", OracleType.NVarChar).Value = dwData["MVGZUSTAND"].ToString(); 
          or1.Parameters.Add("MVGKANR", OracleType.NVarChar).Value = dwData["MVGKANR"].ToString(); 
          or1.Parameters.Add("MVGKAPOS", OracleType.NVarChar).Value = dwData["MVGKAPOS"].ToString(); 
          or1.Parameters.Add("MVGVB", OracleType.NVarChar).Value = dwData["MVGVB"].ToString(); 
          or1.Parameters.Add("MVGVG", OracleType.NVarChar).Value = dwData["MVGVG"].ToString(); 
          or1.Parameters.Add("MVGLOSGR", OracleType.NVarChar).Value = dwData["MVGLOSGR"].ToString(); 
          or1.Parameters.Add("MVGAUFGR", OracleType.NVarChar).Value = dwData["MVGAUFGR"].ToString(); 
          or1.Parameters.Add("MVGGART", OracleType.NVarChar).Value = dwData["MVGGART"].ToString(); 
          or1.Parameters.Add("MVGGVAR", OracleType.NVarChar).Value = dwData["MVGGVAR"].ToString(); 
          or1.Parameters.Add("MVGGFARBE", OracleType.NVarChar).Value = dwData["MVGGFARBE"].ToString(); 
          or1.Parameters.Add("MVGPLANVBR", OracleType.NVarChar).Value = dwData["MVGPLANVBR"].ToString(); 
          or1.Parameters.Add("MVGPLANABF", OracleType.NVarChar).Value = dwData["MVGPLANABF"].ToString(); 
          or1.Parameters.Add("MVGPLANABFPRZ", OracleType.NVarChar).Value = dwData["MVGPLANABFPRZ"].ToString(); 
          or1.Parameters.Add("MVGPLANKOST", OracleType.NVarChar).Value = dwData["MVGPLANKOST"].ToString(); 
          or1.Parameters.Add("MVGSOLLVBR", OracleType.NVarChar).Value = dwData["MVGSOLLVBR"].ToString(); 
          or1.Parameters.Add("MVGSOLLABF", OracleType.NVarChar).Value = dwData["MVGSOLLABF"].ToString(); 
          or1.Parameters.Add("MVGSOLLABFPRZ", OracleType.NVarChar).Value = dwData["MVGSOLLABFPRZ"].ToString(); 
          or1.Parameters.Add("MVGSOLLKOST", OracleType.NVarChar).Value = dwData["MVGSOLLKOST"].ToString(); 
          or1.Parameters.Add("MVGISTVBR", OracleType.NVarChar).Value = dwData["MVGISTVBR"].ToString(); 
          or1.Parameters.Add("MVGISTABF", OracleType.NVarChar).Value = dwData["MVGISTABF"].ToString(); 
          or1.Parameters.Add("MVGISTABFPRZ", OracleType.NVarChar).Value = dwData["MVGISTABFPRZ"].ToString(); 
          or1.Parameters.Add("MVGISTKOST", OracleType.NVarChar).Value = dwData["MVGISTKOST"].ToString(); 
          or1.Parameters.Add("MVGISTKOSTDPR", OracleType.NVarChar).Value = dwData["MVGISTKOSTDPR"].ToString(); 
          or1.Parameters.Add("MVGISTKOSTPPR", OracleType.NVarChar).Value = dwData["MVGISTKOSTPPR"].ToString(); 
          or1.Parameters.Add("MVGFILLER", OracleType.NVarChar).Value = dwData["MVGFILLER"].ToString(); 
                    #endregion
                    break; 
                default:
                    break;
            }


            Connect2Proxy(or1);
        }
    }
}
